package com.example.myweb.Repository.JpaRepository;

import com.example.myweb.Dto.AuthorInfoDTO;
import com.example.myweb.Dto.FeedItemDTO;
import com.example.myweb.Repository.JpaRepository.FeedRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.math.BigInteger;
import java.sql.Timestamp;
import java.util.*;
import java.util.stream.Collectors;

@Repository
public class FeedRepositoryImpl implements FeedRepository {

    @PersistenceContext
    private EntityManager entityManager;

    /**
     * 【findFeed - 完整方法】
     * 根据作者ID列表获取信息流，并聚合图片附件信息。
     */
    @Override
    public Page<FeedItemDTO> findFeed(List<Long> authorIds, Pageable pageable) {
        // --- Step 1: 构建原生SQL查询主体数据 ---
        StringBuilder queryBuilder = new StringBuilder();
        Map<String, Object> parameters = new HashMap<>();

        // 文章部分
        queryBuilder.append("SELECT 'article' as type, a.article_id as id, a.title, a.content as summary, a.publish_time, a.like_count, a.comment_count, a.cover_image_url, u.user_id, u.nickname, u.avatar_url FROM articles a JOIN users u ON a.author_id = u.user_id");
        if (authorIds != null && !authorIds.isEmpty()) {
            queryBuilder.append(" WHERE u.user_id IN (:authorIds)");
            parameters.put("authorIds", authorIds);
        }

        queryBuilder.append(" UNION ALL ");

        // 动态部分
        queryBuilder.append("SELECT 'dynamic' as type, d.dynamic_id as id, NULL as title, d.content as summary, d.publish_time, d.like_count, d.comment_count, NULL as cover_image_url, u.user_id, u.nickname, u.avatar_url FROM dynamics d JOIN users u ON d.author_id = u.user_id");
        if (authorIds != null && !authorIds.isEmpty()) {
            queryBuilder.append(" WHERE u.user_id IN (:authorIds)");
        }

        // 包装成子查询以应用统一的排序和分页
        String finalQuery = "SELECT * FROM (" + queryBuilder.toString() + ") as combined_feed ORDER BY publish_time DESC";

        // 主查询（获取数据）
        Query selectQuery = entityManager.createNativeQuery(finalQuery);
        setParameters(selectQuery, parameters);
        selectQuery.setFirstResult((int) pageable.getOffset());
        selectQuery.setMaxResults(pageable.getPageSize());

        @SuppressWarnings("unchecked")
        List<Object[]> results = selectQuery.getResultList();

        // 如果没有结果，直接返回空页面
        if (results.isEmpty()) {
            // 仍需计算总数以返回正确的分页信息
            String countQueryStr = "SELECT COUNT(*) FROM (" + queryBuilder.toString() + ") as count_feed";
            Query countQuery = entityManager.createNativeQuery(countQueryStr);
            setParameters(countQuery, parameters);
            Long totalElements = ((Number) countQuery.getSingleResult()).longValue();
            return new PageImpl<>(Collections.emptyList(), pageable, totalElements);
        }

        // 将原生SQL结果初步映射到DTO列表
        List<FeedItemDTO> feedItems = results.stream().map(this::mapToObjectDto).collect(Collectors.toList());

        // --- Step 2: 提取所有动态的ID，用于查询附件 ---
        List<Long> dynamicIds = feedItems.stream()
                .filter(item -> "dynamic".equals(item.getType()))
                .map(FeedItemDTO::getId)
                .collect(Collectors.toList());

        Map<Long, List<String>> attachmentMap = new HashMap<>();
        if (!dynamicIds.isEmpty()) {
            // --- Step 3: 使用原生SQL批量查询所有相关附件 ---
            String attachmentQueryStr = "SELECT content_id, url FROM attachments WHERE content_type = 'dynamic' AND type = 'IMAGE' AND content_id IN (:dynamicIds)";
            Query attachmentQuery = entityManager.createNativeQuery(attachmentQueryStr);
            attachmentQuery.setParameter("dynamicIds", dynamicIds);

            @SuppressWarnings("unchecked")
            List<Object[]> attachmentResults = attachmentQuery.getResultList();

            // --- Step 4: 将附件结果处理成 Map<dynamicId, List<url>> ---
            for (Object[] row : attachmentResults) {
                Long contentId = ((Number) row[0]).longValue();
                String url = (String) row[1];
                attachmentMap.computeIfAbsent(contentId, k -> new ArrayList<>()).add(url);
            }
        }

        // --- Step 5: 遍历初步生成的DTO列表，将图片信息组装进去 ---
        for (FeedItemDTO item : feedItems) {
            if ("dynamic".equals(item.getType())) {
                List<String> imageUrls = attachmentMap.getOrDefault(item.getId(), Collections.emptyList());
                item.setImageUrls(imageUrls);
                // 顺便填充封面图
                if (!imageUrls.isEmpty()) {
                    item.setCoverImageUrl(imageUrls.get(0));
                }
            }
        }

        // --- Count查询 ---
        String countQueryStr = "SELECT COUNT(*) FROM (" + queryBuilder.toString() + ") as count_feed";
        Query countQuery = entityManager.createNativeQuery(countQueryStr);
        setParameters(countQuery, parameters);
        Long totalElements = ((Number) countQuery.getSingleResult()).longValue();

        return new PageImpl<>(feedItems, pageable, totalElements);
    }

    /**
     * 【findFeedByIds - 完整方法】
     * 根据文章ID列表和动态ID列表获取信息流，并聚合图片附件信息。
     */
    @Override
    public Page<FeedItemDTO> findFeedByIds(List<Long> articleIds, List<Long> dynamicIds, Pageable pageable) {
        // --- Step 1: 构建原生SQL查询主体数据 ---
        StringBuilder queryBuilder = new StringBuilder();
        Map<String, Object> parameters = new HashMap<>();

        boolean hasArticles = articleIds != null && !articleIds.isEmpty();
        boolean hasDynamics = dynamicIds != null && !dynamicIds.isEmpty();

        if (!hasArticles && !hasDynamics) {
            return Page.empty(pageable);
        }

        // 文章部分
        if (hasArticles) {
            queryBuilder.append("SELECT 'article' as type, a.article_id as id, a.title, a.content as summary, a.publish_time, a.like_count, a.comment_count, a.cover_image_url, u.user_id, u.nickname, u.avatar_url FROM articles a JOIN users u ON a.author_id = u.user_id WHERE a.article_id IN (:articleIds)");
            parameters.put("articleIds", articleIds);
        }

        // 如果文章和动态都有，则用 UNION ALL 连接
        if (hasArticles && hasDynamics) {
            queryBuilder.append(" UNION ALL ");
        }

        // 动态部分
        if (hasDynamics) {
            queryBuilder.append("SELECT 'dynamic' as type, d.dynamic_id as id, NULL as title, d.content as summary, d.publish_time, d.like_count, d.comment_count, NULL as cover_image_url, u.user_id, u.nickname, u.avatar_url FROM dynamics d JOIN users u ON d.author_id = u.user_id WHERE d.dynamic_id IN (:dynamicIds)");
            parameters.put("dynamicIds", dynamicIds);
        }

        // 包装成子查询以应用统一的排序和分页
        String finalQuery = "SELECT * FROM (" + queryBuilder.toString() + ") as combined_feed ORDER BY publish_time DESC";

        // 主查询（获取数据）
        Query selectQuery = entityManager.createNativeQuery(finalQuery);
        setParameters(selectQuery, parameters);
        selectQuery.setFirstResult((int) pageable.getOffset());
        selectQuery.setMaxResults(pageable.getPageSize());

        @SuppressWarnings("unchecked")
        List<Object[]> results = selectQuery.getResultList();

        // 如果没有结果，直接返回空页面
        if (results.isEmpty()) {
            long totalElements = (hasArticles ? articleIds.size() : 0) + (hasDynamics ? dynamicIds.size() : 0);
            return new PageImpl<>(Collections.emptyList(), pageable, totalElements);
        }

        // 将原生SQL结果初步映射到DTO列表
        List<FeedItemDTO> feedItems = results.stream().map(this::mapToObjectDto).collect(Collectors.toList());

        // --- Step 2: 提取所有动态的ID，用于查询附件 ---
        List<Long> dynamicIdsForAttachment = feedItems.stream()
                .filter(item -> "dynamic".equals(item.getType()))
                .map(FeedItemDTO::getId)
                .collect(Collectors.toList());

        Map<Long, List<String>> attachmentMap = new HashMap<>();
        if (!dynamicIdsForAttachment.isEmpty()) {
            // --- Step 3: 使用原生SQL批量查询所有相关附件 ---
            String attachmentQueryStr = "SELECT content_id, url FROM attachments WHERE content_type = 'dynamic' AND type = 'IMAGE' AND content_id IN (:dynamicIds)";
            Query attachmentQuery = entityManager.createNativeQuery(attachmentQueryStr);
            attachmentQuery.setParameter("dynamicIds", dynamicIdsForAttachment);

            @SuppressWarnings("unchecked")
            List<Object[]> attachmentResults = attachmentQuery.getResultList();

            // --- Step 4: 将附件结果处理成 Map<dynamicId, List<url>> ---
            for (Object[] row : attachmentResults) {
                Long contentId = ((Number) row[0]).longValue();
                String url = (String) row[1];
                attachmentMap.computeIfAbsent(contentId, k -> new ArrayList<>()).add(url);
            }
        }

        // --- Step 5: 遍历初步生成的DTO列表，将图片信息组装进去 ---
        for (FeedItemDTO item : feedItems) {
            if ("dynamic".equals(item.getType())) {
                List<String> imageUrls = attachmentMap.getOrDefault(item.getId(), Collections.emptyList());
                item.setImageUrls(imageUrls);
                // 顺便填充封面图
                if (!imageUrls.isEmpty()) {
                    item.setCoverImageUrl(imageUrls.get(0));
                }
            }
        }

        // --- Count查询优化 ---
        long totalElements = (hasArticles ? articleIds.size() : 0) + (hasDynamics ? dynamicIds.size() : 0);

        return new PageImpl<>(feedItems, pageable, totalElements);
    }

    // --- 辅助方法 (请确保它们在类中也存在) ---

    private void setParameters(Query query, Map<String, Object> parameters) {
        for (Map.Entry<String, Object> entry : parameters.entrySet()) {
            query.setParameter(entry.getKey(), entry.getValue());
        }
    }

    private FeedItemDTO mapToObjectDto(Object[] row) {
        FeedItemDTO dto = new FeedItemDTO();
        dto.setType((String) row[0]);
        dto.setId(((Number) row[1]).longValue());
        dto.setTitle((String) row[2]);
        String summary = (String) row[3];
        // 增加对 summary 的 null 检查
        if (summary != null) {
            // 截断逻辑可以根据需要调整
            if (summary.length() > 150) {
                dto.setSummary(summary.substring(0, 150) + "...");
            } else {
                dto.setSummary(summary);
            }
        }
        // 增加对 publish_time 的 null 检查
        if (row[4] != null) {
            dto.setPublishTime(((Timestamp) row[4]).toLocalDateTime());
        }
        // 使用 getInt 或类似方法，并处理可能的 null
        dto.setLikeCount(row[5] != null ? ((Number) row[5]).intValue() : 0);
        dto.setCommentCount(row[6] != null ? ((Number) row[6]).intValue() : 0);
        dto.setCoverImageUrl((String) row[7]);

        AuthorInfoDTO author = new AuthorInfoDTO();
        author.setUserId(((Number) row[8]).longValue());
        author.setNickname((String) row[9]);
        author.setAvatarUrl((String) row[10]);
        dto.setAuthor(author);

        // 初始化imageUrls为空列表，避免前端出现null错误
        dto.setImageUrls(new ArrayList<>());

        return dto;
    }

    // --- 辅助方法 ---
//
//    private void setParameters(Query query, Map<String, Object> parameters) {
//        for (Map.Entry<String, Object> entry : parameters.entrySet()) {
//            query.setParameter(entry.getKey(), entry.getValue());
//        }
//    }
//
//    private FeedItemDTO mapToObjectDto(Object[] row) {
//        FeedItemDTO dto = new FeedItemDTO();
//        dto.setType((String) row[0]);
//        dto.setId(((Number) row[1]).longValue());
//        dto.setTitle((String) row[2]);
//        String summary = (String) row[3];
//        if (summary != null) {
//            dto.setSummary(summary.substring(0, Math.min(summary.length(), 150)) + "...");
//        }
//        dto.setPublishTime(((Timestamp) row[4]).toLocalDateTime());
//        dto.setLikeCount((Integer) row[5]);
//        dto.setCommentCount((Integer) row[6]);
//        dto.setCoverImageUrl((String) row[7]);
//
//        AuthorInfoDTO author = new AuthorInfoDTO();
//        author.setUserId(((Number) row[8]).longValue());
//        author.setNickname((String) row[9]);
//        author.setAvatarUrl((String) row[10]);
//        dto.setAuthor(author);
//
//        return dto;
//    }
}